package icom.acm.manager;

import icom.acm.bean.T_USER;
import icom.acm.util.DBConnect;

import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;


public class T_UserMgr {	
	public int insert(T_USER u){
		int flag = -1;
		String sql = "INSERT INTO T_USER(U_ID, U_PASSWORD, U_EMAIL, U_NICKNAME, U_REGISTERDATE, U_ROLE)" +
				" VALUES(NULL, ?, ?, ?, now(), -1, false)";
		DBConnect db = new DBConnect();
		db.setAutoCommit(false);
		
		db.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);		
		db.setString(1, u.getU_PASSWORD());
		db.setString(2, u.getU_EMAIL());
		db.setString(3, u.getU_NICKNAME());
		try {
			db.excute();
			flag = db.getGeneratedKeys();
			db.commit();
			
		} catch (MySQLIntegrityConstraintViolationException e) {
			// 数据重复
			flag = -1;
		} finally{
			db.setAutoCommit(true);
			db.close();
		}
		return flag;
	}
	/**
	 * 根据邮箱或昵称查找用户
	 * @param u
	 * @return
	 */
	public T_USER selectByEmail(T_USER u){
		T_USER tu = null;
		String sql = "SELECT U_ID, U_PASSWORD, U_EMAIL, U_NICKNAME, U_REGISTERDATE, U_ROLE " +
				"FROM T_USER WHERE U_EMAIL='"+u.getU_EMAIL()+"'";
		List<T_USER> list = select(sql);
		if (list.size()==1)
			tu = list.get(0);
		return tu;
	}
	public T_USER selectByNickName(T_USER u) {
		T_USER tu = null;
		String sql = "SELECT U_ID, U_PASSWORD, U_EMAIL, U_NICKNAME, U_REGISTERDATE, U_ROLE " +
				"FROM T_USER WHERE U_NICKNAME='"+u.getU_NICKNAME()+"'";		
		List<T_USER> list = select(sql);
		if (list.size()==1)
			tu = list.get(0);
		return tu;
	}
	/**
	 * 根据邮箱或昵称查找用户
	 * @param u
	 * @return
	 */
	public T_USER selectByEmail0(T_USER u, DBConnect db){
		T_USER tu = null;
		String sql = "SELECT U_ID, U_PASSWORD, U_EMAIL, U_NICKNAME, U_REGISTERDATE, U_ROLE " +
				"FROM T_USER WHERE U_EMAIL=?";		
		db.prepareStatement(sql);
		
		
		db.setString(1, u.getU_EMAIL());
		db.excuteQuery();
		try {			
			while (db.next()){
				tu = new T_USER(db);				
			}
		} catch(Exception ex){db.close();}finally{}
		return tu;
	}
	/**
	 *根据email和nickname，密码查找
	 * @param u
	 */
	public T_USER selectByEMAILOrNICKNAMEAndPASSWORD(T_USER u) {
		T_USER tu = null;
		String sql = "SELECT U_ID, U_PASSWORD, U_EMAIL, U_NICKNAME, U_REGISTERDATE, U_ROLE " +
				"FROM T_USER WHERE (U_EMAIL=? OR U_NICKNAME=?) AND U_PASSWORD=?";		
		DBConnect db = new DBConnect();
		db.prepareStatement(sql);
		
		
		db.setString(1, u.getU_EMAIL());
		db.setString(2, u.getU_NICKNAME());
		db.setString(3, u.getU_PASSWORD());
		db.excuteQuery();
		try {			
			while (db.next()){
				tu = new T_USER(db);				
			}
		} catch(Exception ex){}finally{db.close();}
		return tu;
	}
	
	public T_USER selectById0(T_USER u, DBConnect db){
		T_USER tu = null;
		boolean flag = false;
		String sql = "SELECT U_ID, U_PASSWORD, U_EMAIL, U_NICKNAME, U_REGISTERDATE, U_ROLE  " +
		"FROM T_USER WHERE U_ID="+u.getU_ID();		
		db.prepareStatement(sql);
		db.excuteQuery();
		try {
			while (db.next()){
				tu = new T_USER(db);				
			}
		} catch(Exception ex){flag = true;}finally{if (flag)db.close();}
		return tu;		
	}
	
	/**
	 *根据email和nickname，密码查找
	 * @param u
	 * @param db
	 * @throws Exception 
	 */
	public T_USER selectByEMAILOrNICKNAMEAndPASSWORD(T_USER u, DBConnect db) throws Exception {
		T_USER tu = null;
		String sql = "SELECT U_ID, U_PASSWORD, U_EMAIL, U_NICKNAME, U_REGISTERDATE, U_ROLE " +
				"FROM T_USER WHERE (U_EMAIL=? OR U_NICKNAME=?) AND U_PASSWORD=?";				
		db.prepareStatement(sql);
		
		
		db.setString(1, u.getU_EMAIL());
		db.setString(2, u.getU_NICKNAME());
		db.setString(3, u.getU_PASSWORD());
		db.excuteQuery();		
		while (db.next()){
			tu = new T_USER(db);				
		}

		return tu;
	}
	public void updateRoleById0(T_USER u, DBConnect db){
		String sql = "UPDATE T_USER SET U_ROLE="+u.getU_ROLE() + " WHERE U_ID="+u.getU_ID();
		db.prepareStatement(sql);
		db.excuteUpdate();
	}
	public int updatePasswordByEmail(T_USER u) {
		DBConnect db = new DBConnect();
		String sql = "UPDATE T_USER SET U_PASSWORD='"+u.getU_PASSWORD() + "' WHERE U_EMAIL='"+u.getU_EMAIL()+"'";
System.out.println(sql);		
		db.prepareStatement(sql);
		int res = db.excuteUpdate();
		db.close();
		return res;
	}
	private List<T_USER> select(String sql){
		List<T_USER> list = new ArrayList<T_USER>();
		DBConnect db = new DBConnect();
		db.prepareStatement(sql);
		db.excuteQuery();
		try {			
			while (db.next()){
				T_USER tu = new T_USER(db);
				
				list.add(tu);
			}
		} catch(Exception ex){}finally{db.close();}
		
		
		return list;
	}
	public void setPwd(final String uid, final String pwd){
		String sql = "UPDATE T_USER SET U_PASSWORD='"+ pwd + "' WHERE U_ID=" + uid;
		DBConnect db = new DBConnect();
		db.prepareStatement(sql);
		db.excuteUpdate();
	}
}
